package com.hqyj.dao.impl;

import com.hqyj.dao.UserDao;
import com.hqyj.pojo.Book;
import com.hqyj.pojo.User;
import com.hqyj.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author cz
 * 2021/8/11 16:01
 */
public class UserDaoImpl implements UserDao {

    @Override
    public void addUser(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "insert into user values(default,?,?,?,default)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getUsername());
            ps.setString(2,user.getPassword());
            ps.setString(3,user.getSalt());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,null);
        }
    }

    @Override
    public User selectUserByName(String username) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        User user = new User();
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select * from user where username = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,username);
            rs = ps.executeQuery();
            while (rs.next()){
                user.setId(rs.getInt("id"));
                user.setUsername(username);
                user.setPassword(rs.getString("password"));
                user.setSalt(rs.getString("salt"));
                user.setRole(rs.getString("role"));
            }
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,rs);
        }
        return user;
    }

    @Override
    public List<Book> selectBookAll() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> list = new ArrayList<>();
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select * from book";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Book book = new Book();
                book.setBookId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setPublisher(rs.getString("publisher"));
                book.setPubDate(rs.getString("pubdate"));
                book.setLend(rs.getInt("lended"));
                book.setReadId(rs.getInt("read_id"));
                list.add(book);
            }
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<Book> selectBooksByReadId(int readId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> list = new ArrayList<>();
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select * from book where read_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,readId);
            rs = ps.executeQuery();
            while (rs.next()){
                Book book = new Book();
                book.setBookId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setPublisher(rs.getString("publisher"));
                book.setPubDate(rs.getString("pubdate"));
                book.setLend(rs.getInt("lended"));
                book.setReadId(rs.getInt("read_id"));
                list.add(book);
            }
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,rs);
        }
        return list;
    }

    @Override
    public void updateBookByBookId(int bookId) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "update book set lended = 0,read_id = 0 where book_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,bookId);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,null);
        }
    }

    @Override
    public List<Book> selectBooksByLendedIsZero() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Book> list = new ArrayList<>();
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select * from book where lended = 0";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Book book = new Book();
                book.setBookId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setPublisher(rs.getString("publisher"));
                book.setPubDate(rs.getString("pubdate"));
                book.setLend(rs.getInt("lended"));
                book.setReadId(rs.getInt("read_id"));
                list.add(book);
            }
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,rs);
        }
        return list;
    }

    @Override
    public void updateBookByBookId(int bookId,int readId) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "update book set lended = 1,read_id = ? where book_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,readId);
            ps.setInt(2,bookId);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,null);
        }
    }

    @Override
    public void insertBook(Book book) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "insert into book values(default,?,?,?,?,default,default)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,book.getBookName());
            ps.setString(2,book.getAuthor());
            ps.setString(3,book.getPublisher());
            ps.setString(4,book.getPubDate());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,null);
        }
    }

    @Override
    public Book selectBookByBookId(int bookId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Book book = new Book();
        try {
            conn = JdbcUtil.getConnection();
            String sql = "select * from book where book_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,bookId);
            rs = ps.executeQuery();
            while (rs.next()){
                book.setBookId(rs.getInt("book_id"));
                book.setBookName(rs.getString("book_name"));
                book.setAuthor(rs.getString("author"));
                book.setPublisher(rs.getString("publisher"));
                book.setPubDate(rs.getString("pubdate"));
                book.setLend(rs.getInt("lended"));
                book.setReadId(rs.getInt("read_id"));
            }
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,rs);
        }
        return book;
    }

    @Override
    public void updateBookByBookId(Book book) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "update book set book_name = ?,author = ?,publisher = ?,pubdate = ? where book_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,book.getBookName());
            ps.setString(2,book.getAuthor());
            ps.setString(3,book.getPublisher());
            ps.setString(4,book.getPubDate());
            ps.setInt(5,book.getBookId());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,null);
        }
    }

    @Override
    public void deleteBook(int bookId) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "delete from book where book_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,bookId);
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,null);
        }
    }

    @Override
    public void updateUserPassword(User user) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JdbcUtil.getConnection();
            String sql = "update user set password = ? where username = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,user.getPassword());
            ps.setString(2,user.getUsername());
            ps.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollBack(conn);
        }finally {
            JdbcUtil.closeResource(conn,ps,null);
        }
    }

}
